#coding=utf-8
import urllib2
import json
import jsonpath
import chardet
import time, datetime
import pymysql.cursors

url = 'https://api.api68.com/pks/getPksHistoryList.do?lotCode=10001'
request = urllib2.Request(url)
response = urllib2.urlopen(request)
html = response.read()

# 把json格式字符串转换成python对象
jsonobj = json.loads(html)

# 从根节点开始，匹配data节点
haomastr = jsonpath.jsonpath(jsonobj, '$..preDrawCode')
haomalist = map(int, haomastr[0].split(','))
haoma = ','.join(str(i) for i in haomalist)

tss1 = jsonpath.jsonpath(jsonobj, '$..preDrawTime')
qishu = jsonpath.jsonpath(jsonobj, '$..preDrawIssue')
timeArray = time.strptime(tss1[0], "%Y-%m-%d %H:%M:%S")
shijian = int(time.mktime(timeArray))

pid = 1
title = "北京赛车"
ctime = int(time.time())

sqlstr = "insert into uk_records (create_time,pid,title,drawcode,drawtime,drawissue) values('%s','%s','%s','%s','%s','%s')" % (
    ctime, pid, title, haoma, shijian, qishu[0])
#print sqlstr

conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='www_16785678_com',
                       password='7WSkpNPeLzwhktRP',
                       db='www_16785678_com',
                       charset="utf8",
                       cursorclass=pymysql.cursors.DictCursor)
#connect()方法用于创建与数据库的连接，里面可以指定参数，这一步只是连接到了数据库，操作数据库还需要下面的游标
cur = conn.cursor()  #通过获取到的conn数据库的cursor方法创建游标

qstr = "select id from uk_records where drawissue='%s'" % (qishu[0])
#print qstr
#print "\r\n"
cur.execute(qstr)
data = cur.fetchone()

if data == None:
    print "查询结果为空，进行数据插入'\r\n'"
    #.decode("utf-8").encode("gb2312")
    #上一行代码跟在上上行后面，windows系统需要,linux不需要
    cur.execute(sqlstr)
    conn.commit()  #提交数据进数据库

    #更新下注表中奖注数，更新金额变动表，更新会员表用户余额
    for j in haomalist:
        n = haomalist.index(j) + 1

        ostr = "select * from uk_odds where pid=1" + " and ordinal=" + str(n)
        #print ostr
        #print "\r\n"
        cur.execute(ostr)
        odds = cur.fetchone()
        odd = odds['car' + str(j)]
        #print odd

        orderstr = "update uk_orders set bonus=car" + str(j) + "*" + str(
            odd) + " where pid=1 and issue=" + str(
                qishu[0]) + " and ordinal=" + str(n)
        print orderstr
        cur.execute(orderstr)
        conn.commit()  #提交数据进数据库

    ctime = int(time.time())
    moneyinstr = "insert into uk_money(money,mid,nickname,issue,ordinal,pid) select bonus,mid,nickname,issue,ordinal,pid from uk_orders where issue=" + str(
        qishu[0]) + " and bonus>0"
    print moneyinstr
    cur.execute(moneyinstr)
    conn.commit()  #提交数据进数据库
    utime = int(time.time())
    # 这个价格是投注的价格，所以下面不能用这个价格，在上面计算好，这里直接价格不再处理
    # lstr="select price from uk_lottery where id=1 and title='北京赛车'"
    # cur.execute(lstr)
    # price = cur.fetchone()
    # if price==None:
    # 	price['price'] =1

    moneyupstr = "update uk_money set create_time=" + str(
        ctime) + ",update_time=" + str(
            utime
        ) + ",`status`=1,title='中奖' where `status`=0 and issue=" + str(
            qishu[0])
    print moneyupstr
    cur.execute(moneyupstr)
    conn.commit()  #提交数据进数据库

cur.close()
conn.close()  #关闭数据库连接

# 所有cur.execute及下面一行、conn = pymysql、cur = conn.cursor()、cur.close() 注释去掉
